package com.hushan.elevator.dao;

import com.hushan.elevator.model.RiskRecord;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

@Repository
public interface RiskRecordRepository extends JpaRepository<RiskRecord, Integer> {

    @Query(value = """
            select \
            rr.record_id,
            rr.device_serial,
            rr.is_star,
            rr.event_id,
            rr.serial_number,
            rr.state,
            rr.created_time,
            rr.finished_time,
            ri.risk_level,
            ri.risk_title,
            ri.risk_type,
            devi.device_id,
            devi.device_name,
            devi.created_by,
            d.name as dept_name,
            d.address as dept_address,
            d.parent_id as dept_parent_id,
            d.ancestors as dept_ancestors,
            d.dept_id, \
            rr.risk_pic
            from \
            risk_record as rr INNER JOIN \
            risk_info as ri INNER JOIN \
            device_info AS devi inner join \
            auth_dept as d \
            ON rr.device_serial = devi.serial_number AND devi.dept_id = d.dept_id And ri.serial_number = rr.serial_number \
            WHERE \
            (:deptId IS NULL OR (FIND_IN_SET(:deptId, d.ancestors) OR d.dept_id = :deptId)) \
            and (:recordId IS NULL OR rr.record_id = :recordId) \
            and (:star IS NULL OR rr.is_star = :star) \
            and (:deviceSerial IS NULL OR devi.serial_number = :deviceSerial) \
            and (:deviceName IS NULL OR devi.device_name LIKE CONCAT('%', :deviceName, '%')) \
            and (:riskType IS NULL OR ri.risk_type = :riskType) \
            and (:riskLevel IS NULL OR ri.risk_level = :riskLevel) \
            and d.is_deleted=0 \
            and devi.is_deleted=0""",
            nativeQuery = true)
    Page<Object[]> findRisk(
            @Param("recordId") Integer recordId,
            @Param("deptId") Integer deptId,
            @Param("deviceName") String deviceName,
            @Param("deviceSerial") String deviceSerial,
            @Param("star") Integer star,
            @Param("riskType") Integer riskType,
            @Param("riskLevel") Integer riskLevel,
            Pageable pageable
    );

    @Query(value = """
            select \
            count(ra.record_id)
            from \
            risk_record as ra INNER JOIN \
            device_info AS devi inner join \
            auth_dept as d \
            ON ra.device_serial = devi.serial_number AND devi.dept_id = d.dept_id
            WHERE (FIND_IN_SET(:deptId,d.ancestors) or d.dept_id = :deptId) and d.is_deleted=0 and devi.is_deleted=0;""", nativeQuery = true)
    int findCountInDept(int deptId);

    @Query(value = """
            SELECT\s
                ri.risk_id,\s
                ri.serial_number,\s
                ri.risk_title,\s
                ri.risk_level,\s
                ri.risk_type,\s
                ri.has_photo,\s
                ri.created_time,\s
                ri.updated_time,\s
                ri.created_by,\s
                ri.is_deleted,
                COUNT(ra.record_id) AS numb\s
            FROM\s
                risk_record AS ra\s
            INNER JOIN\s
                risk_info AS ri\s
            ON\s
                ri.serial_number = ra.serial_number\s
            INNER JOIN\s
                device_info AS devi\s
            ON\s
                devi.serial_number = ra.device_serial\s
            INNER JOIN\s
                auth_dept AS d\s
            ON\s
                devi.dept_id = d.dept_id\s
            WHERE\s
                (d.dept_id = :deptId OR FIND_IN_SET(:deptId, d.ancestors)) AND devi.is_deleted = 0\s
            GROUP BY\s
                ri.serial_number,\s
                ri.risk_id,\s
                ri.risk_title,\s
                ri.risk_level,\s
                ri.risk_type,\s
                ri.has_photo,\s
                ri.created_time,\s
                ri.updated_time,\s
                ri.created_by,\s
                ri.is_deleted\s
            ORDER BY\s
                numb DESC\s
            LIMIT
                :numb;""", nativeQuery = true)
    List<Object[]> staticsRisk(int deptId, int numb);

    List<RiskRecord> findByCreatedTimeBetween(LocalDateTime start, LocalDateTime end);

}
